package db.history;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import db.Database;
import dbservice.history.SystemLogDBService;
import po.SystemLogPo;
import po.Po;

public class SystemLogDB implements SystemLogDBService{
	
	private static final String creatTable = "CREATE TABLE IF NOT EXISTS `seac2`.`systemlog` (  `date` BIGINT(0) NOT NULL,  `operator` VARCHAR(45) NOT NULL,  `operation` VARCHAR(45) NOT NULL,  `object` VARCHAR(45) NOT NULL,  PRIMARY KEY (`date`),  UNIQUE INDEX `date_UNIQUE` (`date` ASC));";
	
	public SystemLogDB(){
		
	}
	
	public static void iniSystemLogDB() throws SQLException{
		Connection c = Database.getConnection();
		Statement s = c.createStatement();
		s.execute(creatTable);
		s.close();
	}
	
	public Po add(SystemLogPo po){
		Po r=new Po();
		Connection c = Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			s.execute("insert into systemlog values('"+po.getDate()+"','"+po.getOperator()+"','"+po.getOperation()+"','"+po.getObject()+"');");
			s.close();
		} catch (SQLException e) {
			r.failed(e.getMessage());
			e.printStackTrace();
		}
		return r;
		
	}

	@Override
	public List<SystemLogPo> get() {
		List<SystemLogPo> result=new ArrayList<SystemLogPo>();		
		try {
			Connection c=Database.getConnection();
			Statement s=c.createStatement();
			ResultSet r;
			r = s.executeQuery("select * from systemlog");
			readResultSet(r,result);
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return result;
	}

	@Override
	public List<SystemLogPo> get(SystemLogPo from, SystemLogPo to) {
		List<SystemLogPo> result=new ArrayList<SystemLogPo>();		
		try {
			Connection c=Database.getConnection();
			Statement s=c.createStatement();
			ResultSet r;
			r = s.executeQuery("select * from systemlog where date>="+from.getDate()+"&&date<="+to.getDate()+";");
			readResultSet(r,result);
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return result;
	}

	private void readResultSet(ResultSet r,List<SystemLogPo> result) throws SQLException{
		while(r.next()){
			SystemLogPo temp=new SystemLogPo(r.getString(2), r.getString(3), r.getString(4));
			result.add(temp);
		}
	}
	
}
